﻿using Kalman.Data.SchemaObject;
using Kalman.Utilities;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Xml.Linq;

namespace Kalman.Data.DbSchemaProvider
{
    /// <summary>
    /// SqlServerSchema
    /// </summary>
    public class SqlServerSchema : DbSchema
    {
        /// <summary>
        ///
        /// </summary>
        /// <param name="db"></param>
        public SqlServerSchema(Database db)
        {
            base.DbProvider = db;
        }

        private Dictionary<string, string> dic = new Dictionary<string, string>();
        private static object syncObject = new object();

        private string GetDBComment(SODatabase db)
        {
            string cmdText = string.Format(@"use [{0}];
                                SELECT value as 'comment'
                                FROM fn_listextendedproperty(default, default, default, default, default, default, default); ", db.Name);

            return this.DbProvider.ExecuteScalar<string>(CommandType.Text, cmdText);
        }

        private string GetTableComment(SOTable table)
        {
            string key = string.Format("{0}_{1}_{2}", table.Database.Name, table.Owner, table.Name);

            lock (syncObject)
            {
                if (dic.ContainsKey(key)) return dic[key];
                else
                {
                    string cmdText = string.Format(@"use [{0}];
                                select  t.name as 'table_name', value as 'comment'
                                    from sys.extended_properties as ep
                                         inner join sys.tables as t on ep.major_id=t.object_id
                                    where class=1 and ep.name='MS_Description' and minor_id=0; ", table.Database.Name, table.Owner);
                    DataTable dt = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText).Tables[0];
                    foreach (DataRow row in dt.Rows)
                    {
                        string tempKey = string.Format("{0}_{1}_{2}", table.Database.Name, table.Owner, row["table_name"].ToString());
                        if (dic.ContainsKey(tempKey)) continue;
                        dic.Add(tempKey, row["comment"].ToString());
                    }
                }

                if (dic.ContainsKey(key)) return dic[key];
                else return table.Name;
            }
        }

        private string GetViewComment(SOView view)
        {
            string key = string.Format("{0}_{1}_{2}", view.Database.Name, view.Owner, view.Name);

            lock (syncObject)
            {
                if (dic.ContainsKey(key)) return dic[key];
                else
                {
                    string cmdText = string.Format(@"use [{0}];
                                select   t.name as 'table_name', value as 'comment'
                                    from sys.extended_properties as ep
                                         inner join sys.views as t on ep.major_id=t.object_id
                                    where class=1 and ep.name='MS_Description' and minor_id=0; ", view.Database.Name, view.Owner);
                    DataTable dt = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText).Tables[0];
                    foreach (DataRow row in dt.Rows)
                    {
                        string tempKey = string.Format("{0}_{1}_{2}", view.Database.Name, view.Owner, row["table_name"].ToString());
                        if (dic.ContainsKey(tempKey)) continue;
                        dic.Add(tempKey, row["comment"].ToString());
                    }
                }

                if (dic.ContainsKey(key)) return dic[key];
                else return view.Name;
            }
        }

        private string GetColumnComment(SOColumn column)
        {
            string key = string.Format("{0}_{1}_{2}", column.Database.Name, column.Parent.Name, column.Name);

            lock (syncObject)
            {
                if (dic.ContainsKey(key)) return dic[key];
                else
                {
                    string cmdText = string.Format(@"use [{0}];
                                                    SELECT major_id, minor_id, t.name AS 'table_name', c.name AS 'column_name', value AS 'comment'
                                                    FROM sys.extended_properties AS ep
                                                    INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
                                                    INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
                                                    WHERE class = 1 and ep.name='MS_Description';", column.Database.Name);
                    if (column.Parent is Kalman.Data.SchemaObject.SOView)
                    {
                        cmdText = string.Format(@"use [{0}];
                                                    SELECT major_id, minor_id, t.name AS 'table_name', c.name AS 'column_name', value AS 'comment'
                                                    FROM sys.extended_properties AS ep
                                                    INNER JOIN sys.views AS t ON ep.major_id = t.object_id
                                                    INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
                                                    WHERE class = 1 and ep.name='MS_Description';", column.Database.Name);
                    }

                    DataTable dt = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText).Tables[0];
                    foreach (DataRow row in dt.Rows)
                    {
                        string tempKey = string.Format("{0}_{1}_{2}", column.Database.Name, row["table_name"].ToString(), row["column_name"].ToString());
                        if (dic.ContainsKey(tempKey)) continue;
                        dic.Add(tempKey, row["comment"].ToString());
                    }
                }

                if (dic.ContainsKey(key)) return dic[key];
                else return column.Name;
            }
        }

        //获取表的主键列
        private List<string> GetPrimaryKeys(SOTable table)
        {
            string cmdText = string.Format(@"use [{2}];exec sp_pkeys '{0}','{1}','{2}';", table.Name, table.Owner, table.Database.Name);
            List<string> list = new List<string>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                list.Add(row["column_name"].ToString());
            }

            return list;
        }

        //获取表的唯一索引列
        private List<string> GetUniqueIndex(SOTable table)
        {
            string cmdText = string.Format(@"select object_name(idx.object_id) as table_name, idx.name as index_name, ic.column_id, ic.index_column_id, ic.object_id, colm.name as col_name
                                                from {2}.sys.indexes idx
                                                     inner join {2}.sys.index_columns ic on idx.index_id=ic.index_id and idx.object_id=ic.object_id
                                                     left join {2}.sys.columns colm on colm.column_id=ic.column_id and colm.object_id=ic.object_id
                                                where idx.is_unique=1 and idx.type=2
                                                and ic.object_id in(select ic1.object_id
					                                                from {2}.sys.index_columns ic1
					                                                where idx.index_id=ic1.index_id and idx.object_id=ic1.object_id
					                                                group by ic1.object_id
					                                                having count(1)=1) and idx.object_id=object_id('{2}.{1}.{0}');
            ;", table.Name, table.Owner, table.Database.Name);
            List<string> list = new List<string>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                list.Add(row["col_name"].ToString());
            }

            return list;
        }

        ////获取表的唯一索引
        //private Dictionary<string, string> GetUniqueIndexes(SOTable table)
        //{
        //    string cmdText = string.Format(@"select object_name(idx.object_id) as table_name, idx.name as index_name,
        //                                        idx_cols=stuff((select ','+convert(varchar, colm2.name)
        //                                                     from {2}.sys.indexes idx2
        //                                                          inner join {2}.sys.index_columns ic2 on idx2.index_id=ic2.index_id and idx2.object_id=ic2.object_id
        //                                                          left join {2}.sys.columns colm2 on colm2.column_id=ic2.column_id and colm2.object_id=ic2.object_id
        //                                                     where idx.object_id=idx2.object_id and idx.name=idx2.name
        //                                                    for xml path('')), 1, 1, '')
        //                                    from {2}.sys.indexes idx
        //                                         inner join {2}.sys.index_columns ic on idx.index_id=ic.index_id and idx.object_id=ic.object_id
        //                                         left join {2}.sys.columns colm on colm.column_id=ic.column_id and colm.object_id=ic.object_id
        //                                    where idx.is_unique=1 and idx.type=2 and idx.is_primary_key=0  and idx.object_id=object_id('{2}.{1}.{0}')
        //                                    group by idx.object_id, idx.name;
        //    ;", table.Name, table.Owner, table.Database.Name);
        //    List<string> list = new List<string>();
        //    DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

        //    Dictionary<string, string> dic = new Dictionary<string, string>();

        //    foreach (DataRow row in dt.Rows)
        //    {
        //        if (!dic.ContainsKey(row["index_name"].ToString()))
        //        {
        //            dic.Add(row["index_name"].ToString(), row["idx_cols"].ToString());
        //        }
        //    }

        //    return dic;
        //}

        public override List<SODatabase> GetDatabaseList()
        {
            List<SODatabase> list = base.GetDatabaseList();
            SortedDictionary<string, SODatabase> dic = new SortedDictionary<string, SODatabase>();
            foreach (var item in list)
            {
                if (item.Name == "master" || item.Name == "model" || item.Name == "msdb" || item.Name == "tempdb") continue;
                dic.Add(item.Name, item);
            }
            return dic.Values.ToList<SODatabase>();
        }

        /// <summary>
        /// 获取表列表
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public override List<SOTable> GetTableList(SODatabase db)
        {
            string cmdText = string.Format("use [{0}];exec sp_tables;", db.Name);
            SortedDictionary<string, SOTable> dic = new SortedDictionary<string, SOTable>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                if (row["TABLE_TYPE"].ToString() != "TABLE") continue;
                if (row["TABLE_OWNER"].ToString() == "sys") continue;
                SOTable table = new SOTable { Parent = db, Name = row["TABLE_NAME"].ToString(), Owner = row["TABLE_OWNER"].ToString() };
                table.SchemaName = table.Owner;
                table.Comment = GetTableComment(table);
                dic.Add(table.Name, table);
            }

            return dic.Values.ToList<SOTable>();
        }

        /// <summary>
        /// 获取表所拥有的列列表
        /// </summary>
        /// <param name="table"></param>
        /// <param name="isNeedDetail"></param>
        /// <returns></returns>
        public override List<SOColumn> GetTableColumnList(SOTable table)
        {
            return GetTableColumnList(table, 1, 0, IsGetDetailTable: true);//顶层获取外键和详细表
        }

        private int MaxDetailTableDeepLevl = 3;
        private int MaxForeignTableDeepLevl = 2;

        /// <summary>
        /// 获取表所拥有的列列表
        /// </summary>
        /// <param name="table"></param>
        /// <param name="IsGetDetailTable">表示仅仅获取外键表</param>
        /// <returns></returns>
        public List<SOColumn> GetTableColumnList(SOTable table, int CurrentDetailTableDeepLevl, int CurrentForeignTableDeepLevl, bool IsGetDetailTable = true)
        {
            string cmdText = string.Format(@"use [{2}];select   colm.name column_name, object_definition(colm.default_object_id) as column_def, systype.name type_name, colm.is_identity, colm.is_nullable ,
                                                        cast(case when systype.name in ('nvarchar', 'nchar') then colm.max_length / 2 else colm.max_length end as int) length, cast(colm.precision as int) precision, cast(colm.scale as int) scale, colm.is_computed,isnull(f.constraint_column_id,0) as is_foreignkey,
			                                             object_name(F.referenced_object_id) as FKTable, D.name FKTableColumn
                                              from     sys.columns colm
                                                inner join sys.types systype on colm.system_type_id = systype.system_type_id and colm.user_type_id = systype.user_type_id
                                                left join sys.foreign_key_columns f on f.parent_object_id=colm.object_id and f.parent_column_id=colm.column_id
			                                    left join sys.columns D on f.referenced_object_id=D.object_id and f.referenced_column_id=D.column_id

                                            where    colm.object_id = object_id('{2}.{1}.{0}')
                                            order by colm.column_id;", table.Name, table.Owner, table.Database.Name);

            List<SOColumn> columnList = new List<SOColumn>();
            List<string> pkList = GetPrimaryKeys(table);
            //List<string> uniqueList = GetUniqueIndex(table);
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
            //string sql = string.Format(@"select  col.name from syscolumns col,sysforeignkeys f where f.fkeyid=col.id and f.fkey=col.colid
            //                                     and OBJECT_NAME(f.fkeyid)  ='{0}'; ", table.Name);

            //string fkcol = this.DbProvider.ExecuteScalar<string>(CommandType.Text, sql);
            Dictionary<string, List<(string, string, SOTable)?>> dicDeatils = new Dictionary<string, List<(string, string, SOTable)?>>();
            if (IsGetDetailTable)
            {
                if (CurrentDetailTableDeepLevl < MaxDetailTableDeepLevl)
                { //为详细表获取各详细子表
                    dicDeatils = GetDetailsList(table, CurrentDetailTableDeepLevl + 1); //注意获取详细表的层次
                }
                // else
                //  dicDeatils = GetDetailsList(table, CurrentDeepLevl, false);
            }

            foreach (DataRow row in dt.Rows)
            {
                SOColumn column = new SOColumn
                {
                    Parent = table,
                    Name = row["column_name"].ToString(),
                    DefaultValue = row["column_def"].ToString(),
                    Nullable = row["is_nullable"].ToString() == "True",
                    NativeType = row["type_name"].ToString(),
                    Identify = row["is_identity"].ToString() == "True",
                    Computed = row["is_computed"].ToString() == "True",
                    ForeignKey = System.Convert.ToBoolean(row["is_foreignkey"]),
                    ForeignKeyTableName = row["FKTable"].ToString(),
                    ForeignKeyTableColumnName = row["FKTableColumn"].ToString(),
                    Length = ConvertUtil.ToInt32(row["length"], -1),
                    Precision = ConvertUtil.ToInt32(row["precision"], -1),
                    Scale = ConvertUtil.ToInt32(row["scale"], -1),
                };
                //为本表列获取各外键表
                if (column.ForeignKey) //&& !column.Name.Contains("Parent")
                {
                    if ((CurrentDetailTableDeepLevl == 2 && CurrentForeignTableDeepLevl < 1) || //其他层中的第二层只取一级外键
                        (CurrentDetailTableDeepLevl == 1 && CurrentForeignTableDeepLevl < MaxForeignTableDeepLevl)) //本层最多取二级外键
                    {
                        SOTable tableForeignKeys = new SOTable { Parent = table.Database, Name = column.ForeignKeyTableName, Owner = table.Owner };
                        tableForeignKeys.SchemaName = table.SchemaName;
                        tableForeignKeys.Comment = GetTableComment(tableForeignKeys);

                        tableForeignKeys.ColumnList = GetTableColumnList(tableForeignKeys, CurrentDetailTableDeepLevl, CurrentForeignTableDeepLevl + 1, false);//注意获取外键表的层次
                        column.ForeignKeyTableInfo = tableForeignKeys;
                    }
                }

                column.PrimaryKey = pkList.Contains(column.Name);
                //  column.Unique = uniqueList.Contains(column.Name);
                column.DataType = this.GetDbType(column.NativeType);
                column.Comment = GetColumnComment(column);

                //为详细表获取各详细子表
                if (IsGetDetailTable && dicDeatils.ContainsKey(column.Name))
                {
                    column.DetailTables = dicDeatils[column.Name];
                }

                columnList.Add(column);
            }

            return columnList;
        }

        // <summary>
        /// 获取表所拥有的详细表列表（字典Key为本表的列名，值为（详细表名，详细表的列名，详细表详情））
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public Dictionary<string, List<(string, string, SOTable)?>> GetDetailsList(SOTable table, int DetailsListDeepLevl)
        {
            Dictionary<string, List<(string, string, SOTable)?>> dic = new Dictionary<string, List<(string, string, SOTable)?>>();
            if (table.Name == "SysDictDetail")//Sys_DictDetail字典表的ID很多表都有引用，没有必要罗列出来。
                return dic;

            string cmdText = string.Format(@"select t.name as table_name, s.name SchemaName, colm.name column_name,  object_name(f.referenced_object_id) as FKTable, D.name FKTableColumn
                    from sys.columns colm
                         left join sys.foreign_key_columns f on f.parent_object_id=colm.object_id and f.parent_column_id=colm.column_id
                         left join sys.columns D on f.referenced_object_id=D.object_id and f.referenced_column_id=D.column_id
                         left join sys.tables as t on colm.object_id=t.object_id
                         left join sys.schemas as s on t.[schema_id]=s.[schema_id]
                    where  f.constraint_column_id=1  and f.referenced_object_id=object_id('{2}.{1}.{0}')
                    order by D.name; ", table.Name, table.Owner, table.Database.Name);

            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            List<(string, string, SOTable)?> list;
            string FKTableColumn;
            foreach (DataRow row in dt.Rows)
            {
                FKTableColumn = row["FKTableColumn"].ToString();

                SOTable tableDetails = new SOTable { Parent = table.Database, Name = row["table_name"].ToString(), Owner = row["SchemaName"].ToString() };

                tableDetails.SchemaName = table.Owner;
                //这里注意同一个表自引用

                tableDetails.Comment = GetTableComment(tableDetails);

                tableDetails.ColumnList = GetTableColumnList(tableDetails, DetailsListDeepLevl, 0, true);//默认获取外键，层次呢？
                tableDetails.IndexList = GetTableIndexList(tableDetails);

                var detailsInfo = (row["table_name"].ToString(), row["column_name"].ToString(), tableDetails);

                if (!dic.ContainsKey(FKTableColumn))
                {
                    list = new List<(string, string, SOTable)?>();
                    list.Add(detailsInfo);
                    dic.Add(FKTableColumn, list);
                }
                else
                {
                    dic[FKTableColumn].Add(detailsInfo);
                }
            }
            return dic;
        }

        /// <summary>
        /// 获取表所拥有的索引列表
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public override List<SOIndex> GetTableIndexList(SOTable table)
        {
            string cmdText = string.Format(@"select object_name(idx.object_id) as table_name, idx.name as index_name,
                    idx.is_unique, idx.is_primary_key, idx.type
                    from {2}.sys.indexes idx
                    where   idx.object_id=object_id('{2}.{1}.{0}'); ", table.Name, table.Owner, table.Database.Name);

            List<SOIndex> indexList = new List<SOIndex>();
            List<SOColumn> columnList = table.ColumnList;
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                SOIndex index = new SOIndex
                {
                    Parent = table,
                    Name = row["index_name"].ToString(),
                    Comment = "",
                    IsCluster = System.Convert.ToInt32(row["type"]) == 1 ? true : false,
                    IsFullText = false,
                    IsPrimaryKey = System.Convert.ToBoolean(row["is_primary_key"]),
                    IsUnique = System.Convert.ToBoolean(row["is_unique"])
                };
                indexList.Add(index);

                string cmdText2 = string.Format(@"select colm.name as col_name
                                            from {1}.sys.indexes idx
                                                 inner join {1}.sys.index_columns ic on idx.index_id=ic.index_id and idx.object_id=ic.object_id
                                                 left join {1}.sys.columns colm on colm.column_id=ic.column_id and colm.object_id=ic.object_id
                                            where idx.name='{0}';", index.Name, table.Database.Name);

                DataTable dt2 = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText2).Tables[0];
                index.Columns = new List<SOColumn>();
                foreach (DataRow row2 in dt2.Rows)
                {
                    foreach (SOColumn column in columnList)
                    {
                        if (row2[0].ToString() == column.Name)
                            index.Columns.Add(column);
                    }
                }
            }

            return indexList;
        }

        /// <summary>
        /// 获取视图列表
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public override List<SOView> GetViewList(SODatabase db)
        {
            string cmdText = string.Format("use [{0}];exec sp_tables;", db.Name);
            SortedDictionary<string, SOView> dic = new SortedDictionary<string, SOView>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                if (row["TABLE_TYPE"].ToString() != "VIEW") continue;
                if (row["TABLE_OWNER"].ToString() == "sys" || row["TABLE_OWNER"].ToString() == "INFORMATION_SCHEMA") continue;
                SOView view = new SOView { Parent = db, Name = row["TABLE_NAME"].ToString(), Owner = row["TABLE_OWNER"].ToString() };
                view.SchemaName = view.Owner;
                view.Comment = GetViewComment(view);
                dic.Add(view.Name, view);
            }

            return dic.Values.ToList<SOView>();
        }

        /// <summary>
        /// 获取视图所拥有的列列表
        /// </summary>
        /// <param name="view"></param>
        /// <returns></returns>
        public override List<SOColumn> GetViewColumnList(SOView view)
        {
            string cmdText = string.Format(@"use [{2}];select   colm.name column_name, object_definition(colm.default_object_id) as column_def, systype.name type_name, colm.is_identity, colm.is_nullable ,
                                                        cast(case when systype.name in ('nvarchar', 'nchar') then colm.max_length / 2 else colm.max_length end as int) length, cast(colm.precision as int) precision, cast(colm.scale as int) scale, colm.is_computed,isnull(f.constraint_column_id,0) as is_foreignkey,
			                                             object_name(F.referenced_object_id) as FKTable, D.name FKTableColumn
                                              from     sys.columns colm
                                                inner join sys.types systype on colm.system_type_id = systype.system_type_id and colm.user_type_id = systype.user_type_id
                                                left join sys.foreign_key_columns f on f.parent_object_id=colm.object_id and f.parent_column_id=colm.column_id
			                                    left join sys.columns D on f.referenced_object_id=D.object_id and f.referenced_column_id=D.column_id

                                            where    colm.object_id = object_id('{2}.{1}.{0}')
                                            order by colm.column_id;", view.Name, view.Owner, view.Database.Name);

            List<SOColumn> columnList = new List<SOColumn>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            //string sql = string.Format(@"select  col.name from syscolumns col,sysforeignkeys f where f.fkeyid=col.id and f.fkey=col.colid
            //                                     and OBJECT_NAME(f.fkeyid)  ='{0}'; ", view.Name);

            //string fkcol = this.DbProvider.ExecuteScalar<string>(CommandType.Text, sql);

            foreach (DataRow row in dt.Rows)
            {
                SOColumn column = new SOColumn
                {
                    Parent = view,
                    Name = row["column_name"].ToString(),
                    DefaultValue = row["column_def"].ToString(),
                    Nullable = row["is_nullable"].ToString() == "True",
                    NativeType = row["type_name"].ToString(),
                    Identify = row["is_identity"].ToString() == "True",
                    Computed = row["is_computed"].ToString() == "True",
                    //  ForeignKey = System.Convert.ToBoolean(row["is_foreignkey"]),
                    //  ForeignKeyTable = row["FKTable"].ToString(),
                    //  ForeignKeyTableColumn = row["FKTableColumn"].ToString(),
                    Length = ConvertUtil.ToInt32(row["length"], -1),
                    Precision = ConvertUtil.ToInt32(row["precision"], -1),
                    Scale = ConvertUtil.ToInt32(row["scale"], -1),
                };

                column.DataType = this.GetDbType(column.NativeType);
                column.Comment = GetColumnComment(column);
                columnList.Add(column);
            }

            return columnList;
        }

        //        /// <summary>
        //        /// 获取视图所拥有的索引列表
        //        /// </summary>
        //        /// <param name="view"></param>
        //        /// <returns></returns>
        //        public override List<SOIndex> GetViewIndexList(SOView view)
        //        {
        //            string cmdText = string.Format(@"SELECT *
        //                FROM INFORMATION_SCHEMA.`constraints`
        //                WHERE table_schema='{0}' AND table_name='{1}';", view.Database.Name, view.Name);

        //            List<SOIndex> indexList = new List<SOIndex>();
        //            List<SOColumn> columnList = GetViewColumnList(view);
        //            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

        //            foreach (DataRow row in dt.Rows)
        //            {
        //                SOIndex index = new SOIndex
        //                {
        //                    Parent = view,
        //                    Name = row["constraint_name"].ToString(),
        //                    Comment = row["constraint_name"].ToString(),
        //                    IsCluster = false,
        //                    IsFullText = row["constraint_type"].ToString() == "Full Text",
        //                    IsPrimaryKey = row["constraint_type"].ToString() == "PRIMARY KEY",
        //                    IsUnique = row["constraint_type"].ToString() == "UNIQUE"
        //                };
        //                indexList.Add(index);

        //                string cmdText2 = string.Format(@"SELECT column_name
        //                FROM INFORMATION_SCHEMA.`statistics`
        //                WHERE table_schema='{0}' AND table_name='{1}';", view.Database.Name, view.Name);

        //                DataTable dt2 = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText2).Tables[0];
        //                index.Columns = new List<SOColumn>();
        //                foreach (DataRow row2 in dt2.Rows)
        //                {
        //                    foreach (SOColumn column in columnList)
        //                    {
        //                        if (row2[0].ToString() == column.Name) index.Columns.Add(column);
        //                    }
        //                }
        //            }

        //            return indexList;
        //        }

        /// <summary>
        /// 获取存储过程列表
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public override List<SOCommand> GetCommandList(SODatabase db)
        {
            string cmdText = string.Format(@"use [{0}];SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_catalog='{0}' AND routine_type='PROCEDURE';", db.Name);

            List<SOCommand> commandList = new List<SOCommand>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                SOCommand command = new SOCommand { Parent = db, Name = row["routine_name"].ToString(), Comment = row["routine_name"].ToString() };
                commandList.Add(command);
            }

            return commandList;
        }

        /// <summary>
        /// 获取存储过程参数列表
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public override List<SOCommandParameter> GetCommandParameterList(SOCommand command)
        {
            string cmdText = string.Format(@"USE [{1}];SELECT routine_definition FROM INFORMATION_SCHEMA.PARAMETERS
                WHERE SPECIFIC_schema='{0}' AND SPECIFIC_type='PROCEDURE' AND SPECIFIC_catalog='{1}' AND SPECIFIC_name='{2}';",
                                                    command.Owner ?? "dbo", command.Database.Name, command.Name);

            List<SOCommandParameter> columnList = new List<SOCommandParameter>();
            DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                ParameterDirection direction = ParameterDirection.ReturnValue;
                string pmode = row["PARAMETER_MODE"].ToString();
                if (pmode == "IN") direction = ParameterDirection.Input;
                if (pmode == "OUT") direction = ParameterDirection.Output;
                if (pmode == "INOUT") direction = ParameterDirection.InputOutput;

                SOCommandParameter param = new SOCommandParameter
                {
                    Parent = command,
                    Name = row["PARAMETER_NAME"].ToString(),
                    Direction = direction,
                    NativeType = row["DATA_TYPE"].ToString().Replace(" identity", ""),
                    Length = ConvertUtil.ToInt32(row["CHARACTER_OCTET_LENGTH"], -1),
                    Precision = ConvertUtil.ToInt32(row["NUMERIC_PRECISION"], -1),
                    Scale = ConvertUtil.ToInt32(row["NUMERIC_SCALE"], -1),
                };

                param.DataType = this.GetDbType(param.NativeType);
                columnList.Add(param);
            }

            return columnList;
        }

        ///// <summary>
        ///// 获取表的Sql脚本
        ///// </summary>
        ///// <param name="table"></param>
        ///// <returns></returns>
        //public override string GetTableSqlText(SOTable table)
        //{
        //    throw new NotImplementedException();
        //}

        /// <summary>
        /// 获取视图的Sql脚本
        /// </summary>
        /// <param name="view"></param>
        /// <returns></returns>
        public override string GetViewSqlText(SOView view)
        {
            string cmdText = string.Format("use [{0}];SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE table_schema='{1}' AND table_name='{2}';", view.Database.Name, view.Owner, view.Name);
            string text = this.DbProvider.ExecuteScalar(System.Data.CommandType.Text, cmdText).ToString();
            return text;
        }

        /// <summary>
        /// 获取存储过程的Sql脚本
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public override string GetCommandSqlText(SOCommand command)
        {
            string cmdText = string.Format(@"USE [{1}];SELECT routine_definition FROM INFORMATION_SCHEMA.ROUTINES
                WHERE routine_schema='{0}' AND routine_type='PROCEDURE' AND routine_catalog='{1}' AND routine_name='{2}';",
                                                    command.Owner ?? "dbo", command.Database.Name, command.Name);

            string text = this.DbProvider.ExecuteScalar(System.Data.CommandType.Text, cmdText).ToString();
            return text;
        }

        public override System.Data.DbType GetDbType(string nativeType)
        {
            return TypeUtil.SqlServerDataType2DbType(nativeType);
        }
    }
}